Plugins/Community Based Plugins/Microsoft Defender for Cloud Custom plugin Scenarios/DevSecOpsCustomPlugInWithMDC/GetLastDevSecOpsAlerts.yaml

Descriptor:
  Name: DevSecOps
  DisplayName: DevSecOps by Defender for Cloud
  Description:  KQL queries for retrieving the results of the Code Scanning (CodeQL), Dependency Scanning (Dependabot), Secret Scanning  
    and Infrastructure as Code (IaC) scanning made by GitHub Advanced Security (GhAS) in GitHub and by GitHub Advanced Security for Azure DevOps (GAzDO)
    in Azure DevOps, as collected by the Defender Cloud Security Posture Management (CSPM) component of Microsoft Defender for Cloud (MDC).
    These KQL queries allow to track the changes of these results over time. For example, it is possible to extract only the evidences (alerts) that
    were added in the last X days. 
SkillGroups:
  - Format: KQL
    Skills:
      - Name: GetNewDevSecOpsAlertsInTheLastTimespan
        DisplayName: Get the new alerts for DevSecOps in the last timespan  
        Description: Get the the new DevSecOps results that were added, during the specified past timespan, in the recommendations in Defender for Cloud related to DevOps Security.
          These results are the alerts produced by the Code Scanning (CodeQL), Dependency Scanning (Dependabot), Secret Scanning and Infrastructure as Code (IaC)
          scanning made by GitHub Advanced Security (GhAS) in GitHub and by GitHub Advanced Security for Azure DevOps (GAzDO) in Azure DevOps.
          Each of these recommendations includes these fields 
              AssessedResourceType (AdoRepo for Azure DevOps repository, GitHubRepo for GitHub repository), 
              RepositoryUri (URL of the repo in Azure DevOps or GitHub), 
              Category (Code for Code Scanning with CodeQL, dependency for dependency scanning, secrets for secrets scanning and infrastruture as code for IaC scanning), 
              RecommendationSeverity, 
              RecommendationState, 
              RecommendationName, 
              AssessedResourceId, 
              Description, 
              CodeQLRuleName (name of the CodeQL rule that raised the alert - Only for Code scanning alerts), 
              CodeQLRuleDescription (description of the CodeQL rule that raised the alert - Only for Code scanning alerts), 
              CodeQLRuleSeverity (severity of the CodeQL rule that raised the alert - Only for Code scanning alerts), 
              CodeQLFile (file identified by the CodeQL rule that raised the alert - Only for Code scanning alerts and secrets scanning alerts), 
              CodeQLLine (line in the file identified by the CodeQL rule or by the secret scanning that raised the alert - Only for Code scanning alerts and secrets scanning alerts), 
              IaCFile (file identified by the Infrastructure as Code [IaC] rule that raised the alert - Only for Code IaC alerts), 
              State, 
              Tool_name (name of the tool that raised the alert), 
              DepSecurityAdvisoryCVE (CVE for the dependency - only for dependency scanning), 
              DepSecurityAdvisoryCVSS (CVSS of the CVE for the dependency - only for dependency scanning),
              DepSecurityAdvisoryLink (Link for the CVE for the dependency - only for dependency scanning),
              DepSecurityVulnPkgType (Type of package of the dependency with the CVE - only for dependency scanning),
              DepSecurityVulnPkgName (Name of package of the dependency with the CVE - only for dependency scanning),
              DepSecurityVulnVerRange (Versions of package of the dependency with the CVE - only for dependency scanning)          
          While DevOps Security in Defender for Cloud supports also the code scanning results in devops environments based on GitLab Enterprise, this KQL
          query at this time does not support retriving results coming from that specific source. 
        Inputs:
          - Name: TimespanBack
            Description: Provide the time interval in the past, starting now, within which new alarms should be searched for. (e.g. 1d for 1 day, 4h for 4 hours) 
            Required: true
        Settings:
          Target: LogAnalytics
          TenantId: [Write your Tenant Id]
          SubscriptionId: [Write your Subscription Id]
          ResourceGroupName: [Write the name of your resource group]
          WorkspaceName: [Write the name of your Log Analytics or Sentinel workspace]      
          Template: |-
            let timeInt = {{TimespanBack}};
            let res = (
            SecurityNestedRecommendation
            | extend AssessedResourceType = tostring(parse_json(AdditionalData).Data.AssessedResourceType)
            | where AssessedResourceType =~ "adoRepo" or AssessedResourceType =~ "GitHubRepo"
            | extend CodeQLRuleName = iif(Category=='Dependency','',iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.Rule_name),tostring(parse_json(AdditionalData).Data.RuleId)))
            | extend CodeQLRuleDescription = iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.Rule_description),tostring(parse_json(AdditionalData).Data.RuleDescription))
            | extend CodeQLRuleSeverity = iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.Rule_severity),tostring(parse_json(AdditionalData).Data.Severity))
            | extend State = tostring(parse_json(AdditionalData).Data.State)
            | extend CodeQLFile = iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.Path),tostring(parse_json(AdditionalData).Data.File))
            | extend CodeQLLine = iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.Line),tostring(parse_json(AdditionalData).Data.Line))
            | extend IaCFile = iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.Path),tostring(parse_json(AdditionalData).Data.Path))
            | extend Tool_name = iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.Tool_name),tostring(parse_json(AdditionalData).Data.ToolName))
            | extend RepositoryUri = iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.RepositoryUri),iif(tostring(parse_json(AdditionalData).Data.RepositoryUri)!="",tostring(parse_json(AdditionalData).Data.RepositoryUri),tostring(parse_json(AdditionalData).Data.ProjectUri)))
            | extend DepSecurityAdvisoryCVE = iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.SecurityAdvisoryCVE),iif(Category=='Dependency','?',''))
            | extend DepSecurityAdvisoryCVSS = iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.securityAdvisory__cvss__score),iif(Category=='Dependency','?',''))
            | extend DepSecurityAdvisoryLink = iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.securityAdvisory__permalink),iif(Category=='Dependency','?',''))
            | extend DepSecurityVulnPkgType = iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.securityVulnerability__package__ecosystem),iif(Category=='Dependency','?',''))
            | extend DepSecurityVulnPkgName = iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.securityVulnerability__package__name),iif(Category=='Dependency','?',''))
            | extend DepSecurityVulnVerRange = iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.securityVulnerability__vulnerableVersionRange),iif(Category=='Dependency','?',''))
            | project TimeGenerated, SubAssessmentTimeGeneration, AssessedResourceType, RepositoryUri, Category, RecommendationSeverity, RecommendationState, RecommendationName, AssessedResourceId, Description, CodeQLRuleName, CodeQLRuleDescription, CodeQLRuleSeverity, CodeQLFile, CodeQLLine, IaCFile, State, Tool_name, DepSecurityAdvisoryCVE, DepSecurityAdvisoryCVSS, DepSecurityAdvisoryLink, DepSecurityVulnPkgType, DepSecurityVulnPkgName, DepSecurityVulnVerRange
            );
            let lastRes = res | where SubAssessmentTimeGeneration >= ago(timeInt);
            let prevRes = res | where SubAssessmentTimeGeneration < ago(timeInt);
            lastRes 
            | join kind=anti prevRes on AssessedResourceType, RepositoryUri, Category, RecommendationSeverity, RecommendationState, RecommendationName, AssessedResourceId, Description, CodeQLRuleName, CodeQLRuleDescription, CodeQLRuleSeverity, CodeQLFile, CodeQLLine, IaCFile, State, Tool_name, DepSecurityAdvisoryCVE, DepSecurityAdvisoryCVSS, DepSecurityAdvisoryLink, DepSecurityVulnPkgType, DepSecurityVulnPkgName, DepSecurityVulnVerRange
      - Name: GetDevSecOpsAlertsInTheLastTimespan
        DisplayName: Get the unique alerts for DevSecOps in the last timespan. 
        Description: Get the the DevSecOps unique results that were added, during the specified past timespan, in the recommendations in Defender for Cloud related to DevOps Security.
          These results are the alerts produced by the Code Scanning (CodeQL), Dependency Scanning (Dependabot), Secret Scanning and Infrastructure as Code (IaC)
          scanning made by GitHub Advanced Security (GhAS) in GitHub and by GitHub Advanced Security for Azure DevOps (GAzDO) in Azure DevOps.
          Each of these recommendations includes these fields 
              AssessedResourceType (AdoRepo for Azure DevOps repository, GitHubRepo for GitHub repository), 
              RepositoryUri (URL of the repo in Azure DevOps or GitHub), 
              Category (Code for Code Scanning with CodeQL, dependency for dependency scanning, secrets for secrets scanning and infrastruture as code for IaC scanning), 
              RecommendationSeverity, 
              RecommendationState, 
              RecommendationName, 
              AssessedResourceId, 
              Description, 
              CodeQLRuleName (name of the CodeQL rule that raised the alert - Only for Code scanning alerts), 
              CodeQLRuleDescription (description of the CodeQL rule that raised the alert - Only for Code scanning alerts), 
              CodeQLRuleSeverity (severity of the CodeQL rule that raised the alert - Only for Code scanning alerts), 
              CodeQLFile (file identified by the CodeQL rule that raised the alert - Only for Code scanning alerts and secrets scanning alerts), 
              CodeQLLine (line in the file identified by the CodeQL rule or by the secret scanning that raised the alert - Only for Code scanning alerts and secrets scanning alerts), 
              IaCFile (file identified by the Infrastructure as Code [IaC] rule that raised the alert - Only for Code IaC alerts), 
              State, 
              Tool_name (name of the tool that raised the alert), 
              DepSecurityAdvisoryCVE (CVE for the dependency - only for dependency scanning), 
              DepSecurityAdvisoryCVSS (CVSS of the CVE for the dependency - only for dependency scanning),
              DepSecurityAdvisoryLink (Link for the CVE for the dependency - only for dependency scanning),
              DepSecurityVulnPkgType (Type of package of the dependency with the CVE - only for dependency scanning),
              DepSecurityVulnPkgName (Name of package of the dependency with the CVE - only for dependency scanning),
              DepSecurityVulnVerRange (Versions of package of the dependency with the CVE - only for dependency scanning), 
              FirstOccurrence (First occurrence of the unique alerts with all the above fields), 
              LastOccurrence (Last occurrence of the unique alerts with all the above fields),
              NumberOfOccurrences (Number of occurrence of the unique alerts with all the above fields)
          While DevOps Security in Defender for Cloud supports also the code scanning results in devops environments based on GitLab Enterprise, this KQL
          query at this time does not support retriving results coming from that specific source. 
        Inputs:
          - Name: TimespanBack
            Description: Provide the time interval in the past, starting now, within which new alarms should be searched for. (e.g. 1d for 1 day, 4h for 4 hours) 
            Required: true
        Settings:
          Target: LogAnalytics
          TenantId: [Write your Tenant Id]
          SubscriptionId: [Write your Subscription Id]
          ResourceGroupName: [Write the name of your resource group]
          WorkspaceName: [Write the name of your Log Analytics or Sentinel workspace]      
          Template: |-
            let timeInt = {{TimespanBack}};
            let res = (
            SecurityNestedRecommendation
            | where SubAssessmentTimeGeneration >= ago(timeInt)
            | extend AssessedResourceType = tostring(parse_json(AdditionalData).Data.AssessedResourceType)
            | where AssessedResourceType =~ "adoRepo" or AssessedResourceType =~ "GitHubRepo"
            | extend CodeQLRuleName = iif(Category=='Dependency','',iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.Rule_name),tostring(parse_json(AdditionalData).Data.RuleId)))
            | extend CodeQLRuleDescription = iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.Rule_description),tostring(parse_json(AdditionalData).Data.RuleDescription))
            | extend CodeQLRuleSeverity = iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.Rule_severity),tostring(parse_json(AdditionalData).Data.Severity))
            | extend State = tostring(parse_json(AdditionalData).Data.State)
            | extend CodeQLFile = iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.Path),tostring(parse_json(AdditionalData).Data.File))
            | extend CodeQLLine = iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.Line),tostring(parse_json(AdditionalData).Data.Line))
            | extend IaCFile = iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.Path),tostring(parse_json(AdditionalData).Data.Path))
            | extend Tool_name = iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.Tool_name),tostring(parse_json(AdditionalData).Data.ToolName))
            | extend RepositoryUri = iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.RepositoryUri),iif(tostring(parse_json(AdditionalData).Data.RepositoryUri)!="",tostring(parse_json(AdditionalData).Data.RepositoryUri),tostring(parse_json(AdditionalData).Data.ProjectUri)))
            | extend DepSecurityAdvisoryCVE = iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.SecurityAdvisoryCVE),iif(Category=='Dependency','?',''))
            | extend DepSecurityAdvisoryCVSS = iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.securityAdvisory__cvss__score),iif(Category=='Dependency','?',''))
            | extend DepSecurityAdvisoryLink = iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.securityAdvisory__permalink),iif(Category=='Dependency','?',''))
            | extend DepSecurityVulnPkgType = iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.securityVulnerability__package__ecosystem),iif(Category=='Dependency','?',''))
            | extend DepSecurityVulnPkgName = iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.securityVulnerability__package__name),iif(Category=='Dependency','?',''))
            | extend DepSecurityVulnVerRange = iif(AssessedResourceType=="GitHubRepo",tostring(parse_json(AdditionalData).Data.securityVulnerability__vulnerableVersionRange),iif(Category=='Dependency','?',''))
            | project TimeGenerated, SubAssessmentTimeGeneration, AssessedResourceType, RepositoryUri, Category, RecommendationSeverity, RecommendationState, RecommendationName, AssessedResourceId, Description, CodeQLRuleName, CodeQLRuleDescription, CodeQLRuleSeverity, CodeQLFile, CodeQLLine, IaCFile, State, Tool_name, DepSecurityAdvisoryCVE, DepSecurityAdvisoryCVSS, DepSecurityAdvisoryLink, DepSecurityVulnPkgType, DepSecurityVulnPkgName, DepSecurityVulnVerRange
            );
            res
            | summarize FirstOccurrence = min(SubAssessmentTimeGeneration), LastOccurrence = max(SubAssessmentTimeGeneration), NumberOfOccurrences=count() by AssessedResourceType, RepositoryUri, Category, RecommendationSeverity, RecommendationState, RecommendationName, AssessedResourceId, Description, CodeQLRuleName, CodeQLRuleDescription, CodeQLRuleSeverity, CodeQLFile, CodeQLLine, IaCFile, State, Tool_name, DepSecurityAdvisoryCVE, DepSecurityAdvisoryCVSS, DepSecurityAdvisoryLink, DepSecurityVulnPkgType, DepSecurityVulnPkgName, DepSecurityVulnVerRange